package br.folhapgto.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

import br.folhapgto.dao.banco.Conexao;
import br.folhapgto.dao.idao.ITaxaServicoDAO;
import br.folhapgto.model.TaxaServico;
import java.util.ArrayList;

public class TaxaServicoDAO implements ITaxaServicoDAO{

	private static TaxaServicoDAO instanciaUnica = new TaxaServicoDAO();

    public static TaxaServicoDAO getInstanciaUnica()
    {
        return instanciaUnica;
    }

    // Construtor
    public TaxaServicoDAO()
    {

    }

    // M�todos CRUD
    public TaxaServico carregaDados(ResultSet rs) throws SQLException
    {
    	TaxaServico taxaServico = new TaxaServico();

    	taxaServico.setIdTaxaServico(rs.getInt("idTaxaServico"));
    	taxaServico.setIdEmpregado(rs.getInt("idEmpregado"));
    	taxaServico.setTaxaServico(rs.getFloat("taxaServico"));
        taxaServico.setMesReferencia(rs.getInt("mesReferencia"));
        taxaServico.setAnoReferencia(rs.getInt("anoReferencia"));

        return taxaServico;
    }

    public Integer gravar(TaxaServico taxaServico)
    {

        int retornoBanco = 0;
        Connection conn = Conexao.getInstanciaUnica().criaConexao();

        if (conn != null) {
            PreparedStatement pstmt = null;
            try {
                pstmt = conn.prepareStatement(
                        "INSERT INTO TB_TAXA_SERVICO (idEmpregado, " +
                        "taxaServico, " +
                        "mesReferencia," +
                        "anoReferencia)" +
                        " VALUES (?, ?, ?, ?)");
                pstmt.setInt(1, taxaServico.getIdEmpregado());
                pstmt.setFloat(2, taxaServico.getTaxaServico());
                pstmt.setInt(3, taxaServico.getMesReferencia());
                pstmt.setInt(4, taxaServico.getAnoReferencia());

				retornoBanco = pstmt.executeUpdate();
            } catch (SQLException e) {
                System.out.println("Inclusao Falhou!!!\n" + e.getMessage());
            } finally {
                try {
                    if (pstmt != null) {
                        pstmt.close();
                    }
                    if (conn != null) {
                        conn.close();
                    }
                } catch (SQLException e) {
                    System.out.println(e.getMessage());
                }
            }
        }

        return retornoBanco;
    }

    public List<TaxaServico> lerTodos()
    {
    	Statement stmt = null;
        List<TaxaServico> taxasServico = new ArrayList<TaxaServico>();

        Connection conn = Conexao.getInstanciaUnica().criaConexao();

        if (conn != null) {
            try {
                stmt = conn.createStatement();
                ResultSet rs = stmt.executeQuery(
                        "SELECT * FROM TB_TAXA_SERVICO");
                rs.last();
                rs.beforeFirst();
                while (rs.next()) {
                	taxasServico.add(carregaDados(rs));
                }
                conn.close();
            } catch (SQLException ex) {
                ex.printStackTrace();
            } finally {
                try {
                    if (stmt != null) {
                        stmt.close();
                    }
                    if (conn != null) {
                        conn.close();
                    }
                } catch (SQLException e) {
                    System.out.println(e.getMessage());
                }
            }
        }
        return taxasServico;
    }

	public TaxaServico ler(Integer idTaxaServico)
	{
		Statement stmt = null;
		TaxaServico taxaServico = null;
        Connection conn = Conexao.getInstanciaUnica().criaConexao();

        if (conn != null) {
            try {
                stmt = conn.createStatement();
                ResultSet rs = stmt.executeQuery(
                        "SELECT * FROM TB_TAXA_SERVICO WHERE idTaxaServico = " +
                        idTaxaServico);
                if (rs.next()) {
                	taxaServico = carregaDados(rs);
                } else {
                	taxaServico = null;
                }
                conn.close();
            } catch (SQLException ex) {
                ex.printStackTrace();
            } finally {
                try {
                    if (stmt != null) {
                        stmt.close();
                    }
                    if (conn != null) {
                        conn.close();
                    }
                } catch (SQLException e) {
                    System.out.println(e.getMessage());
                }
            }
        }
        return taxaServico;
	}

	public Integer remover(Integer idTaxaServico)
	{
		int n = 0;
        Statement stmt = null;
        Connection conn = Conexao.getInstanciaUnica().criaConexao();

        if (conn != null) {
            try {
                stmt = conn.createStatement();
                n = stmt.executeUpdate(
                        "DELETE FROM TB_TAXA_SERVICO WHERE idTaxaServico = " +
                        idTaxaServico);
                conn.close();
            } catch (SQLException ex) {
                ex.printStackTrace();
            } finally {
                try {
                    if (stmt != null) {
                        stmt.close();
                    }
                    if (conn != null) {
                        conn.close();
                    }
                } catch (SQLException e) {
                    System.out.println(e.getMessage());
                }
            }
        }
        return n;
	}

	public Integer alterar(TaxaServico taxaServico)
	{
            int n = 0;
            Connection conn = Conexao.getInstanciaUnica().criaConexao();

            if (conn != null) {
                PreparedStatement pstmt = null;
                try {
                    pstmt = conn.prepareStatement(
                            "UPDATE TB_TAXA_SERVICO SET " +
                            "idEmpregado = ?, " +
                            "taxaServico = ?, " +
                            "mesReferencia = ?, " +
                            "anoReferencia = ? " +
                            "WHERE idTaxaServico = ?");

                    pstmt.setInt(1, taxaServico.getIdEmpregado());
                    pstmt.setFloat(2, taxaServico.getTaxaServico());
                    pstmt.setInt(3, taxaServico.getMesReferencia());
                    pstmt.setInt(4, taxaServico.getIdTaxaServico());
                    pstmt.setInt(5, taxaServico.getAnoReferencia());

                    n = pstmt.executeUpdate();
                    conn.close();

                } catch (SQLException ex) {
                    ex.printStackTrace();
                } finally {
                    try {
                        if (pstmt != null) {
                            pstmt.close();
                        }
                        if (conn != null) {
                            conn.close();
                        }
                    } catch (SQLException e) {
                        System.out.println(e.getMessage());
                    }
                }
            }
        return n;
	}
}